#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@Date:2022/07/18 16:51:28
'''
import sys
from Common.Utils import Utils
sys.path.append(".")

from db_logic.db_base import Repository
from models.humiture_models import EntityHumitureRecord
# from common.utils import Utils

#日志信息业务逻辑类
class BllHumitureRecord(Repository):
    #_instance_lock = threading.Lock()
    ##实现单例模式
    #def __new__(cls, *args, **kwargs):
    #    if not hasattr(BllHumitureRecord, "_instance"):
    #        with BllHumitureRecord._instance_lock:
    #            if not hasattr(BllHumitureRecord, "_instance"):
    #                BllHumitureRecord._instance = object.__new__(cls)
    #    return BllHumitureRecord._instance

    def __init__(self, entityType=EntityHumitureRecord):
        return super().__init__(entityType)
    
    def get_data_info_list(self, client_id, time_type="1", obj_type="1"):
        # 1.24小时，2近七天，3.近一个月
        time_dic = {
            "1": "record_date >=(NOW() - interval 24 hour)",
            "2": "DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(record_date)",
            "3": "DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(record_date)"
        }
        time_str = time_dic.get(time_type)
        # 1湿度，2温度，3voc
        obj_type_dict = {
            "1": "temperature",
            "2": "humidity",
            "3": "voc_content",
        }
        obj_type_str = obj_type_dict.get(obj_type)
        filter_base = ""
        if client_id:
            filter_base += f" and client_id='{client_id}'"

        sql_all = f"""
        select {obj_type_str} data_value, record_date from rms_humiture_record where {time_str} {filter_base} order by record_date
        """

        return self.execute(sql_all).fetchall()


    # 获取每日记录，按时间，柜体分组展示
    def get_client_day_log(self, client_id, start_time, end_time, page_param):
        filter_base = ""
        if client_id:
            filter_base += f" client_id='{client_id}'"
        if start_time and end_time:
            if filter_base:
                filter_base += " and "
            filter_base += f" record_date>= '{start_time}' and record_date<= '{end_time}' "
        if filter_base:
            filter_base = f" where {filter_base} "
        
        sql_all = f"""
            select  a.client_id, a.client_name, a.day_date, a.avg_wd, a.avg_sd, a.avg_voc, b.place  
            from (
                select client_id, client_name,
                DATE_FORMAT(record_date,'%Y-%m-%d') day_date,
                round(avg(temperature),2) avg_wd, 
                round(avg(humidity),2) avg_sd,
                round(avg(voc_content),2) avg_voc
            from rms_humiture_record  {filter_base} GROUP BY client_id, DATE_FORMAT(record_date,'%Y-%m-%d')
            ) a LEFT JOIN rms_client as b on a.client_id=b.client_id ORDER BY day_date desc
        """
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        if page_param:
            page_param.totalRecords = count_number
            sql_all = Utils.sql_paging_assemble(sql_all, page_param)
        return self.execute(sql_all).fetchall()

    # 获取每日记录详情
    def get_client_day_log_info(self, client_id, start_time, end_time, page_param):
        filter_base = ""
        if client_id:
            filter_base += f" client_id='{client_id}'"
        if start_time and end_time:
            if filter_base:
                filter_base += " and "
            filter_base += f" record_date>= '{start_time}' and record_date<= '{end_time}' "
        if filter_base:
            filter_base = f" where {filter_base}"
        sql_all = f"""
        select 
            DATE_FORMAT(record_date,'%H:%i:%s') day_date,
            round(temperature,2) as wd,
            round(humidity,2) as sd,
            round(voc_content,2) as voc
            from rms_humiture_record {filter_base} ORDER BY record_date
        """
        try:
            count_number = len(self.execute(sql_all).fetchall())
        except Exception:
            count_number = 0
        page_param.totalRecords=count_number
        page_sql = Utils.sql_paging_assemble(sql_all, page_param)
        return self.execute(page_sql).fetchall()

    # 获取温湿度列表
    # def getHumitureList(self, customerId, pageParam):
    #     queryStr = 'select * from ((select * from rms_humiture_record where client_id=:client_id) '
    #     queryStr += ' union all (select * from rms_humiture_record where client_id!=:client_id  order by client_name ASC ) )t order by t.record_date DESC '

    #     queryCountStr = 'select COUNT(*) from ((select * from rms_humiture_record where client_id=:client_id) '
    #     queryCountStr += ' union all (select * from rms_humiture_record where client_id!=:client_id  order by client_name ASC ) )t order by t.record_date DESC '
    #     # queryParams = {"clientId": CurrentInfo.ClientInfo.client_id}
    #     queryParams = {"client_id": customerId}
    #     templateList = self.execute(queryStr + ' limit ' + str((pageParam.curPage - 1)
    #                                 * pageParam.pageRows) + ',' + str(pageParam.pageRows), queryParams).fetchall()
    #     pageParam.totalRecords = self.execute(
    #         queryCountStr, queryParams).fetchone()[0]
    #     jsonData = Utils.mysqlTable2Model(templateList)
    #     return jsonData

    def insert_one(self, entity):
        self.insert(entity)
    
    # def get_month(self, num):
    #     import arrow
    #     month_list = []
    #     a = arrow.now()  # 当前本地时间
    #     for i in range(0, num + 1):
    #         yearmonth = a.shift(hours=-i).format("YYYY-MM-DD HH:mm:ss")
    #         month_list.append(yearmonth)
    #     month_list.sort()
    #     return month_list
    
    # def inster_log_info_list(self):
    #     import random
    #     date_list = self.get_month(100)
    #     data_list = []
    #     for i in date_list:
    #         obj = EntityHumitureRecord(
    #             client_id='1c39cb24-07f8-11ed-abd4-f47b094925e1',
    #             client_name='测试机器',
    #             temperature=round(random.uniform(-1, 1),2),
    #             humidity=round(random.uniform(-1, 1),2),
    #             record_date=i,
    #             voc_content=round(random.uniform(-1, 1),2)
    #         )
    #         data_list.append(obj)
    #     self.insert_many(data_list)





if __name__ == '__main__':
    data = BllHumitureRecord().get_data_info_list('')
    print(data)
    for i in data:
        print(i)
    